package com.seio.edc.util;


import com.nova.edc.model.system.CustomerInfo;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;

public class ExcelReadUtil {

    private static int titleIndex;  //变量所在行号-1
    private static int dataIndex;   //数据所在行号-1
    private static Log log = LogFactory.getLog(CustomerInfo.class);

    public static Object excelToData(int titleindex, int dataindex, Object object, @RequestParam(value = "file", required = false) MultipartFile file) throws IOException {
        titleIndex = titleindex;
        dataIndex = dataindex;
        String path = file.getOriginalFilename();
        if (path == null || "".equals(path)) {
            System.out.println("no file");
            return null;
        } else {
            String postfix = path.substring(path.lastIndexOf(".") + 1, path.length());
            if (!"".equals(postfix)) {
                if ("xls".equals(postfix)) {
                    Object arrayList = readXls(object, file);
                    return arrayList;
                } else if ("xlsx".equals(postfix)) {
                    Object arrayList = readXlsx(object, file);
                    return arrayList;
                } else {
                    return null;
                }
            } else {
                System.out.println(path + "Not the Excel file!");
                return null;
            }
        }
    }

    /**
     * Read the Excel 2010
     */
    public static Object readXlsx(Object object, MultipartFile file) throws IOException {
        Object arrayList = new ArrayList();
        Class obj = object.getClass();
        InputStream is = file.getInputStream();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            for (int rowNum = dataIndex; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {//
                int nullcell = 0;
                Object setobject = null;
                try {
                    setobject = obj.newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow == null || xssfRow.getLastCellNum() == -1) {
                    continue;
                }
                int cells = xssfRow.getPhysicalNumberOfCells();
                Class bean = object.getClass();
                for (int i = 0; i < xssfRow.getLastCellNum(); i++) {
                    XSSFCell exceltitle = xssfSheet.getRow(titleIndex).getCell(i);
                    String propertys = getValue(exceltitle);
                    if (xssfRow != null) {
                        XSSFCell no = xssfRow.getCell(i);

                        if (no != null) {
                            if (!"".equals(getValue(no))) {
                                Method setMethod = getSetMethod(bean, propertys);
                                if (null == setMethod) {
                                    continue;
                                }
                                setValue(setobject, getValue(no), setMethod, propertys);
                            } else {
                                nullcell++;
                            }
                        }
                    }
                }
                if (nullcell < xssfRow.getPhysicalNumberOfCells()) {
                    try {
                        Method addmethod = arrayList.getClass().getMethod("add", new Class[]{Object.class});
                        addmethod.invoke(arrayList, new Object[]{setobject});
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return arrayList;
    }

    /**
     * Read the Excel 2003
     */
    public static Object readXls(Object object, MultipartFile file) throws IOException {
        Object arrayList = new ArrayList();
        Class obj = object.getClass();
        InputStream is = file.getInputStream();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            for (int rowNum = dataIndex; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {//
                int nullcell = 0;
                Object setobject = null;
                try {
                    setobject = obj.newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null || hssfRow.getLastCellNum() == -1) {
                    continue;
                }
                Class bean = object.getClass();
                for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
                    HSSFCell exceltitle = hssfSheet.getRow(titleIndex).getCell(i);
                    String propertys = getValue(exceltitle);
                    if (hssfRow != null) {
                        HSSFCell no = hssfRow.getCell(i);
                        if (no != null) {
                            if (!"".equals(getValue(no))) {
                                Method setMethod = getSetMethod(bean, propertys);
                                if (null == setMethod) {
                                    continue;
                                }
                                setValue(setobject, getValue(no), setMethod, propertys);
                            } else {
                                nullcell++;
                            }
                        }
                    }
                }
                if (nullcell < hssfRow.getPhysicalNumberOfCells()) {
                    try {
                        Method addmethod = arrayList.getClass().getMethod("add", new Class[]{Object.class});
                        addmethod.invoke(arrayList, new Object[]{setobject});
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return arrayList;
    }


    public static String getValue(XSSFCell xssfRow) {
        DecimalFormat df = new DecimalFormat("#");
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(df.format(xssfRow.getNumericCellValue()));
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }


    public static String getValue(HSSFCell hssfCell) {
        DecimalFormat df = new DecimalFormat("#");
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(df.format(hssfCell.getNumericCellValue()));
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }




    public static Method getSetMethod(Class objectClass, String fieldName) {
        Field[] fields = objectClass.getDeclaredFields();
        try {
            Class[] parameterTypes = new Class[1];
            Field field = objectClass.getDeclaredField(fieldName);
            parameterTypes[0] = field.getType();
            StringBuffer sb = new StringBuffer();
            sb.append("set");
            sb.append(fieldName.substring(0, 1).toUpperCase());
            sb.append(fieldName.substring(1));
            Method method = objectClass.getMethod(sb.toString(), parameterTypes);
            return method;
        } catch (Exception e) {
            // e.printStackTrace();
            log.info("方法生成失败");
        }

        return null;

    }

    public static void setValue(Object object, String value, Method setMethod, String propertys) {
        try {
            Field field = object.getClass().getDeclaredField(propertys);
            //判断值的类型后进行强制类型转换
            String textValue = null;
            String type = field.getType().toString();//得到此属性的类型
            if (type.endsWith("String")) {
                setMethod.invoke(object, new Object[]{String.valueOf(value)});     //给属性设值
            } else if (type.endsWith("int") || type.endsWith("Integer")) {
                setMethod.invoke(object, new Object[]{(int) Float.parseFloat(value)});       //给属性设值
            } else {
                System.out.println(field.getType() + "\t");
            }
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
    }

}


